***********spruce up cost data***********
import delimited "$data/CCC/childcare_costs_2018.csv", clear
replace v1 = subinstr(v1, " (tie)", "", .)
gen state = word(v1, 2)
replace state = word(v1, 2) + " " +  word(v1, 3) if wordcount(v1) == 4
gen cost = word(v1, -1)
drop v1
replace cost = subinstr(cost, "\$", "", .)
replace cost = subinstr(cost, ",", "", .)
destring cost, replace
replace state = subinstr(state, ":", "", .)
replace state = "District of Columbia" if strpos(state, "D.C")

//get fips codes
ren state state_full
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
drop if statefips == 11
ren cost ccc
xtile ccc_quant = ccc, nq(2)

preserve
replace ccc = ccc/1000
//maptile ccc, geo(state) rev n(9) legdecimal(1)
//graph export "$output/state_ccc.png", replace

//maptile ccc_quant, geo(state) rev n(3) legdecimal(1)
//graph export "$output/state_ccc_quant.png", replace

//graph close
restore

keep ccc* statefips
save "$temp/state_ccc", replace

***********spruce up ACS data***********
//first: get husband location variables
use "$data/ACS/usa_00021", clear
keep if relate == 1
keep if bpl<60
keep serial year bpl
ren bpl bpl_sp
save "$temp/spouse_bpls", replace

***********spruce up ACS data***********
use "$data/ACS/usa_00021", clear

//limit sample
keep if sex == 2 //women
keep if age>=22 & age<=35 //age range
keep if bpl<60 //US natives

preserve
keep if relate == 2
merge 1:1 serial year using "$temp/spouse_bpls", keep(1 3) nogen
save "$temp/acs_with_sp_bpls", replace
restore

drop if relate == 2
append using "$temp/acs_with_sp_bpls"

//drop unnecessary variables
drop raced educd hispand gq hhwt cbserial sample related empstatd ind migrate1d ///
migcounty1 migtype1  region migmet bpld

//clean up and creation of a few simple variables
drop if migrate1 == 4 //abroad last year
drop migrate1
drop if educ<3 //at least one year of high school
tab educ

//college dummies and continuous experience variables
gen hs_drop = (educ<6)
gen hs = (educ == 6)
gen some_coll = (educ>6 & educ<10)
gen coll = (educ == 10)
gen grad = (educ == 11)

replace educ = educ + 6
gen exp = age- (educ + 6)
gen exp_2 = exp^2
gen exp_3 = exp^3
gen exp_4 = exp^4

//demographic dummies
gen black = (race == 2)
gen hisp = (hispan>0)
gen married = (marst<=2)

//nonsensicla values of yngch
replace yngch = -1 if yngch == 99
drop if age-yngch<14 

//want household heads of head spouses. Also allow for children to permit boomerang migration
keep if relate <=4

//employment status
gen emp = 0
replace emp = 1 if empstat == 1

//topcoded hours worked
drop if uhrswork > 75
replace uhrswork = . if uhrswork == 0

//drop negative values of income and construct spousal income variable
drop if inctot<0
drop if ftotinc<0
gen inc_spouse = 0
replace inc_spouse = ftotinc - inctot if (relate == 1 | relate == 2)
drop if inc_spouse<0



//handle past migration variable
ren migplac1 statefips_prev
replace statefips_prev = statefip if statefips_prev == 0
gen mig = (statefips_prev!=statefip) //migration dummy
drop if statefips_prev == 110 //kill Puerto Rico

*****************Code CCC and amenity values for current, previous, and birth state******************
//current state
local amens `"college unemp violent property pop govt_exp s_t_ratio cooling"'
ren statefip statefips
merge m:1 statefips using "$temp/state_ccc", keep(match) nogen
merge m:1 statefips using "$temp/state_amenities_collapse", keep(match) nogen
ren statefips temp
ren ccc ccc_current
ren ccc_quant ccc_quant_current
foreach var in `amens'{
	ren `var' `var'_current
}

//previous
ren statefips_prev statefips
merge m:1 statefips using "$temp/state_ccc", keep(match) nogen
merge m:1 statefips using "$temp/state_amenities_collapse", keep(match) nogen
ren ccc ccc_prev
ren ccc_quant ccc_quant_prev
ren statefips statefips_prev
foreach var in `amens'{
	ren `var' `var'_prev
}

//bpl ccc
ren bpl statefips
merge m:1 statefips using "$temp/state_ccc", keep(match) nogen
merge m:1 statefips using "$temp/state_amenities_collapse", keep(match) nogen
ren ccc ccc_bpl
ren ccc_quant ccc_quant_bpl
ren statefips bpl
ren temp statefips
foreach var in `amens'{
	ren `var' `var'_bpl
}

//adjust ccc variables and rename a few things
foreach var in `amens'{
	ren `var'_current `var'
}

ren ccc_current ccc
ren ccc_quant_current ccc_quant
replace ccc = ccc/1000
replace ccc_prev = ccc_prev/1000
replace ccc_bpl = ccc_bpl/1000

//indicators for young children, home migration, and living in bpl
//gen baby = (yngch == 0 | yngch == 1 & nchild == 1) //so, looking at women with their first young child
//gen baby = (yngch == 0 | yngch == 1) //so, looking at women with their first young child
gen baby = (yngch == 0 | yngch == 1 | yngch == 2 | yngch == 3 | yngch == 4)
gen preg_first = (yngch == 0  & nchild == 1)
gen home_mig = 0
replace home_mig = 1 if mig & (statefips == bpl | statefips == bpl_sp)
gen same_state = (bpl == statefips | bpl_sp == statefips)
gen same_state_prev = (statefips_prev == bpl | statefips_prev == bpl_sp)

//replace home_mig = 1 if mig & statefips == bpl
//gen same_state = (bpl == statefips)
//gen same_state_prev = (statefips_prev == bpl)

gen age_2 = age^2
replace labforce = labforce - 1
gen full_time = 0
replace full_time = 1 if uhrswork>=30 & labforce

//three-value fertility status variable
gen fertility = 0
replace fertility = 1 if preg_first
replace fertility = 2 if baby & !preg_first

forval i = 1/2{
	gen ccc_quant_`i' = (ccc_quant == `i')
}



save "$temp/acs_base", replace



















//end of do-file